﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using QualityRpt.Dao;
using System.Data.Odbc;
using Org.SqlHelper;
using QualityRpt.Utils;

namespace QualityRpt.Dao.Db2
{
    class ServiceInfoDao:IServiceInfoDao
    {
        const string cachePrefix = "SIL-";
        public DataRow Save(DataRow row)
        {
            object rt = null;
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "INSERT INTO RPT.SERVICE_INFO(JOB_NAME,JOB_TYPE,P_ID,JOB_START_TIME,JOB_END_TIME,DATA_START_TIME,DATA_END_TIME,IS_PAUSE,USER_ID,TEMP_ID,CREATE_TIME,DATA_STATUS) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",
                    ODBCHelper.CreateInputParameter("JOB_NAME", OdbcType.VarChar, 50, row["JOB_NAME"]),
                    ODBCHelper.CreateInputParameter("JOB_TYPE", OdbcType.Int, row["JOB_TYPE"]),
                    ODBCHelper.CreateInputParameter("P_ID", OdbcType.Int, row["P_ID"]),
                    ODBCHelper.CreateInputParameter("JOB_START_TIME", OdbcType.DateTime, row["JOB_START_TIME"]),
                    ODBCHelper.CreateInputParameter("JOB_END_TIME", OdbcType.DateTime, row["JOB_END_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_START_TIME", OdbcType.DateTime, row["DATA_START_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_END_TIME", OdbcType.DateTime, row["DATA_END_TIME"]),
                    ODBCHelper.CreateInputParameter("IS_PAUSE", OdbcType.SmallInt, row["IS_PAUSE"]),
                    ODBCHelper.CreateInputParameter("USER_ID", OdbcType.Int, row["USER_ID"]),
                    ODBCHelper.CreateInputParameter("TEMP_ID", OdbcType.Int, row["TEMP_ID"]),
                    ODBCHelper.CreateInputParameter("CREATE_TIME", OdbcType.DateTime, row["CREATE_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_STATUS", OdbcType.SmallInt, row["DATA_STATUS"]),
					ODBCHelper.CreateInputParameter("NOTE", OdbcType.VarChar, 1024, row["NOTE"])
                    );
                rt = ODBCHelper.ExecuteScalar(conn, CommandType.Text, "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            if (rt != null)
            {
                row["ID"] = Convert.ToInt64(rt);
                return row;
            }
            return null;
        }

        public DataRow Update(DataRow row)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, 
                    "UPDATE RPT.SERVICE_INFO SET JOB_NAME=?,JOB_TYPE=?,P_ID=?,JOB_START_TIME=?,JOB_END_TIME=?,DATA_START_TIME=?,DATA_END_TIME=?,IS_PAUSE=?,USER_ID=?,TEMP_ID=?,CREATE_TIME=?,DATA_STATUS=?,NOTE=? WHERE ID=?",
                    ODBCHelper.CreateInputParameter("JOB_NAME", OdbcType.VarChar, 50, row["JOB_NAME"]),
                    ODBCHelper.CreateInputParameter("JOB_TYPE", OdbcType.Int, row["JOB_TYPE"]),
                    ODBCHelper.CreateInputParameter("P_ID", OdbcType.Int, row["P_ID"]),
                    ODBCHelper.CreateInputParameter("JOB_START_TIME", OdbcType.DateTime, row["JOB_START_TIME"]),
                    ODBCHelper.CreateInputParameter("JOB_END_TIME", OdbcType.DateTime, row["JOB_END_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_START_TIME", OdbcType.DateTime, row["DATA_START_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_END_TIME", OdbcType.DateTime, row["DATA_END_TIME"]),
                    ODBCHelper.CreateInputParameter("IS_PAUSE", OdbcType.SmallInt, row["IS_PAUSE"]),
                    ODBCHelper.CreateInputParameter("USER_ID", OdbcType.Int, row["USER_ID"]),
                    ODBCHelper.CreateInputParameter("TEMP_ID", OdbcType.Int, row["TEMP_ID"]),
                    ODBCHelper.CreateInputParameter("CREATE_TIME", OdbcType.DateTime, row["CREATE_TIME"]),
                    ODBCHelper.CreateInputParameter("DATA_STATUS", OdbcType.SmallInt, row["DATA_STATUS"]),
					ODBCHelper.CreateInputParameter("NOTE", OdbcType.VarChar, 1024, row["NOTE"]),
                    ODBCHelper.CreateInputParameter("ID", OdbcType.Int, row["ID"])
                    );
            }
            CacheManager.RemoveByPrefix(cachePrefix);
            return row;
        }

        public DataRow Get(long id)
        {
            DataTable table = GetList("ID=" + id.ToString());
            if (table.Rows.Count > 0)
            {
                return table.Rows[0];
            }
            return null;
        }

        public void Delete(long id)
        {
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                ODBCHelper.ExecuteNonQuery(conn, CommandType.Text, "DELETE FROM RPT.SERVICE_INFO WHERE ID=?",
                    ODBCHelper.CreateInputParameter("ID", OdbcType.BigInt, id));
            }
            CacheManager.RemoveByPrefix(cachePrefix);
        }

        public DataTable GetList(string where)
        {
            return GetList(where, "ID DESC");
        }

        public DataTable GetList(string where, string sort)
        {
            StringBuilder sbText = new StringBuilder();
            sbText.Append("SELECT * FROM RPT.SERVICE_INFO ");
            if (where.Trim() != string.Empty)
            {
                sbText.Append(" WHERE " + where);
            }
            if (sort.Trim() != string.Empty)
            {
                sbText.Append(" ORDER BY " + sort);
            }
            DataTable rtTable = null;
            string cacheKey = cachePrefix + sbText.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbText.ToString());
                }
                CacheManager.Put(cacheKey, rtTable,1);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        public DataTable GetPageList(PageUtils pu, string where, string sort)
        {
            StringBuilder sbListSql = new StringBuilder();
            StringBuilder sbCountSql = new StringBuilder();
            sbListSql.Append("SELECT S.* FROM RPT.SERVICE_INFO AS S ");
            if (where.Trim() != string.Empty)
            {
                sbListSql.Append(" WHERE " + where);
            }
            sbCountSql.Append("WITH WT AS ("+sbListSql.ToString()+") SELECT COUNT(*) FROM WT");
            if (sort.Trim() != string.Empty)
            {
                sbListSql.Append(" ORDER BY " + sort);
            }
            else
            {
                sbListSql.Append(" ORDER BY S.ID DESC");
            }
            DataTable rtTable = null;
            string cacheKey = cachePrefix + sbListSql.ToString() + "-" + pu.Page;
            pu.ListSql = sbListSql.ToString();pu.TotalCountSql = sbCountSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                rtTable = pu.GetDb2OdbcPageList();
                CacheManager.Put(cacheKey, rtTable,1);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
                pu.DB2OdbcExecutePage();
            }
            return rtTable;
        }
		/// <summary>
		/// 获得job_type = 0 的任务的数据截至时间
		/// </summary>
		public DateTime? GetMaxDataEndTime(){
			StringBuilder sbSql = new StringBuilder ();
			DateTime? maxTime = null;
			sbSql.Append ("SELECT MAX(DATA_END_TIME) FROM RPT.SERVICE_INFO WHERE JOB_TYPE=0");//获取合并抽取任务的
			using (OdbcConnection conn = ODBCHelper.GetConnection())
			{
				DataTable rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
				DataRow row = rtTable.Rows [0];
				if (row [0] != DBNull.Value) {
					maxTime = Convert.ToDateTime (row [0]);
				} 
			}
			return maxTime;
		}
    }
}
